﻿using Dapper;
using StudentManagement.IRepository;
using StudentManagement.Model.DTO;
using StudentManagement.Model.Entitys;
using StudentManagement.Model.Enums;
using StudentManagement.Tools;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace StudentManagement.Repository
{
    /// <summary>
    /// 学生仓储
    /// </summary>
    public class StudentRepository : RepositoryBase<StudentEntity>, IStudentRepository
    {
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        public void Delete(int id)
        {
            Connection.Execute("DELETE Student WHERE id=@id", new { id });
        }

        /// <summary>
        /// 根据Id获取实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public StudentEntity GetById(int id)
        {
            return QueryFirstOrDefault("SELECT * FROM Student WHERE id=@id", new { id });
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="qs"></param>
        /// <returns></returns>
        public DataTable GetData(string qs)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.AppendLine("select ");
            sqlBuilder.AppendLine("Number AS [学号]");
            sqlBuilder.AppendLine(",a.[Name] AS [姓名]");
            sqlBuilder.AppendLine($",CASE Sex WHEN 1  THEN N'{SexEmun.Male.ToText()}' ELSE N'{SexEmun.Female.ToText()}' END AS [性别]");
            sqlBuilder.AppendLine(",Age AS [年龄]");
            sqlBuilder.AppendLine(",DateBirth AS [出生日期]");
            sqlBuilder.AppendLine(",CreationTime AS [创建时间]");
            sqlBuilder.AppendLine(",b.[Name] AS [所在班级]");

            sqlBuilder.AppendLine("from Student a");
            sqlBuilder.AppendLine("join Classes b on a.ClassesId = b.Id");

            SqlWhereList whereList = new SqlWhereList();

            if (!string.IsNullOrEmpty(qs))
            {
                whereList.Add("a.[Name] LIKE @Name");
            }

            sqlBuilder.Append(whereList.ToString());

            sqlBuilder.AppendLine("order by a.id DESC");

            using var list = Connection.ExecuteReader(sqlBuilder.ToString(), new { Name = $"%{qs}%" });

            var data = new DataTable();
            data.Load(list);

            return data;
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="entity"></param>
        public void Insert(StudentEntity entity)
        {
            const string sql = "INSERT Student( " +
                                nameof(entity.Number) +
                                "," + nameof(entity.Name) +
                                "," + nameof(entity.Sex) +
                                "," + nameof(entity.Age) +
                                "," + nameof(entity.ClassesId) +
                                "," + nameof(entity.CreationTime) +
                                "," + nameof(entity.DateBirth) +
                                ") values(" +
                                "@" + nameof(entity.Number) +
                                ",@" + nameof(entity.Name) +
                                ",@" + nameof(entity.Sex) +
                                ",@" + nameof(entity.Age) +
                                ",@" + nameof(entity.ClassesId) +
                                ",@" + nameof(entity.CreationTime) +
                                ",@" + nameof(entity.DateBirth) +
                                ")";

            entity.CreationTime = DateTime.Now;

            Connection.Execute(sql, new
            {
                entity.Number,
                entity.Name,
                entity.Sex,
                entity.Age,
                entity.ClassesId,
                entity.CreationTime,
                entity.DateBirth,
            });
        }

        /// <summary>
        /// 学号是否存在
        /// </summary>
        /// <returns></returns>
        public bool NumberIsExist(string number)
        {
            return QueryIsExist("Number=@number", new { number });
        }

        /// <summary>
        /// 学号是否存在,排除当前用户
        /// </summary>
        /// <returns></returns>
        public bool NumberIsExist(string number, int id)
        {
            return QueryIsExist("Number=@number AND Id<>@id", new { number, id });
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="qs"></param>
        /// <returns></returns>
        public Tuple<List<StudentQueryDTO>, int> Search(string qs, int limit, int page)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.AppendLine("select ");
            sqlBuilder.AppendLine("a.Id");
            sqlBuilder.AppendLine(",Number");
            sqlBuilder.AppendLine(",a.[Name]");
            sqlBuilder.AppendLine(",Sex");
            sqlBuilder.AppendLine(",Age");
            sqlBuilder.AppendLine(",DateBirth");
            sqlBuilder.AppendLine(",CreationTime");
            sqlBuilder.AppendLine(",b.[Name] as ClassesName");
            sqlBuilder.AppendLine("from Student a");
            sqlBuilder.AppendLine("join Classes b on a.ClassesId = b.Id");

            SqlWhereList whereList = new SqlWhereList();

            if (!string.IsNullOrEmpty(qs))
            {
                whereList.Add("a.[Name] LIKE @Name");
            }

            sqlBuilder.Append(whereList.ToString());

            var list = QueryPagingList<StudentQueryDTO>(
                sqlBuilder.ToString(),
                limit,
                page,
                "id DESC",
                new { Name = $"%{qs}%" }
                );

            list.Item1.ForEach((item) => item.SexText = item.Sex.ToText());

            return list;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="entity"></param>
        public void Update(StudentEntity entity)
        {
            const string sql = "UPDATE [Student] " +
                "SET " + nameof(entity.Name) + "=@" + nameof(entity.Name) +
                "," + nameof(entity.Age) + "=@" + nameof(entity.Age) +
                "," + nameof(entity.ClassesId) + "=@" + nameof(entity.ClassesId) +
                "," + nameof(entity.DateBirth) + "=@" + nameof(entity.DateBirth) +
                "," + nameof(entity.Sex) + "=@" + nameof(entity.Sex) +
                " WHERE " + nameof(entity.Id) + "=@" + nameof(entity.Id);

            Connection.Execute(sql,
                new
                {
                    entity.Name,
                    entity.Sex,
                    entity.Age,
                    entity.ClassesId,
                    entity.DateBirth,
                    entity.Id,
                });
        }
    }
}
